/**
 * 项目名：五位一体化工安全信息平台
 * 日期：    2019-12-07 19:44:55
 * Copyright (c) 2015- joysuch-版权所有
 */

package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.entity.BpWorkSafetyCert;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import javax.persistence.Tuple;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * BpWorkSafetyCertDao
 */
public interface BpWorkSafetyCertDao
        extends JpaRepository<BpWorkSafetyCert, Long>, QuerydslPredicateExecutor<BpWorkSafetyCert> {
    public Page<BpWorkSafetyCert> findAll(Specification<BpWorkSafetyCert> spec, Pageable pageable);

    public BpWorkSafetyCert save(BpWorkSafetyCert bean);

    public void delete(BpWorkSafetyCert bean);

    public Page<BpWorkSafetyCert> findByType(String type, Pageable pageable);

    // 统计特种作业数量。8大作业证就是特种作业，且通过审核，并 未验收成功
    // @Query("select count(m) from BpWorkSafetyCert m where m.state=2 and ( m.checkState is NULL or m.checkState != 1)")
    @Query("select count(m) from BpWorkSafetyCert m where m.state= 4 ")
    public long specialWorkCount();

    // 统计特种作业数量。8大作业证就是特种作业，且通过审核，并 未验收成功
    @Query("select count(m) as count,m.workLevel as workLevel,m.type as type from BpWorkSafetyCert m where m.state=2 and ( m.checkState is NULL or m.checkState != 1) and (m.endTime >= ?1 and m.startTime < ?2) group by m.type,m.workLevel")
    List<Tuple> inProgressCertsStats(Date start, Date end);

    /**
     * 查找所有未处理的作业证。作业证状态未标记为已完成，同时作业证已验收
     *
     * @param endTime
     * @return
     */
    @Query("select m from BpWorkSafetyCert m where (m.jobFinished is null or m.jobFinished = 0) and m.checkState=1")
    public List<BpWorkSafetyCert> findUnProcessedJob();

    /**
     * 查询所有执行中的作业证
     *
     * @return
     */
    @Query("select m from BpWorkSafetyCert m where m.state=2 and ( m.checkState is NULL or m.checkState != 1)")
    public List<BpWorkSafetyCert> findInProgressingJob();


    /**
     * 查询所有待申请的作业列表
     *
     * @return
     */

    @Query("select m from BpWorkSafetyCert m where (m.createBy=?1  and m.state in(0,3))")
    public List<BpWorkSafetyCert> findPendingWorkApplyList(Long userId);

    /**
     * 查询所有待审批 的作业列表
     *
     * @return
     */

    @Query("select m from BpWorkSafetyCert m where  m.state =1 ")
    public List<BpWorkSafetyCert> findPendingWorkApproveList();


    /**
     * 查询所有 待验收的作业列表
     *
     * @return
     */

    @Query("select m from BpWorkSafetyCert m where m.state =2")
    public List<BpWorkSafetyCert> findPendingWorkAcceptList();


    public BpWorkSafetyCert findByCode(String code);

    int countByRiskPointId(Long riskPointId);

    List<BpWorkSafetyCert> findByState(int i);

    @Query(value = "select m from BpWorkSafetyCert m where m.state = 1 or m.state = 3")
    List<BpWorkSafetyCert> findAPPlyAndFailedAudit();


    @Query(value = "select m from BpWorkSafetyCert m where m.type = ?1 and m.state = 1")
    List<BpWorkSafetyCert> findByTemplatleType(String type);

    @Query(value = "select m from BpWorkSafetyCert m where m.type = ?1 and m.workLevel =?2 and m.state = 1")
    List<BpWorkSafetyCert> findByTypeAndWorkLevel(String type, Integer workLevel);

    List<BpWorkSafetyCert> findByStateBetween(Integer state1, Integer state2);

    @Query(value = "select m.* from wwyt_bp_work_safety_cert m where DATE_FORMAT(m.start_time, '%Y-%m-%d')<=:date and " +
            "DATE_FORMAT(m.end_time, '%Y-%m-%d')>=:date ", nativeQuery = true)
    List<BpWorkSafetyCert> findByTime(String date);

    @Query(value = "select m.* from wwyt_bp_work_safety_cert m where DATE_FORMAT(m.start_time, '%Y-%m-%d')>=:startDate or " +
            "DATE_FORMAT(m.start_time, '%Y-%m-%d')<=:endDate or DATE_FORMAT(m.end_time, '%Y-%m-%d')>=:startDate " +
            "or DATE_FORMAT(m.end_time, '%Y-%m-%d')<=:endDate", nativeQuery = true)
    List<BpWorkSafetyCert> findByTime(String startDate, String endDate);

    @Query(value = "select m.* from wwyt_bp_work_safety_cert m where (m.start_time between :startTime and :endTime) or " +
            "(m.end_time between :startTime and :endTime) or " +
            "(m.start_time <= :startTime and m.end_time >=:endTime)", nativeQuery = true)
    List<BpWorkSafetyCert> findByStartTimeBetweenOrEndTimeBetween(LocalDateTime startTime, LocalDateTime endTime);

    @Query(value = "select m.* from wwyt_bp_work_safety_cert m where m.risk_district_id is not null or " +
            "m.risk_point_id is not null", nativeQuery = true)
    List<BpWorkSafetyCert> findByRisk();

    @Query(value = "select m.* from wwyt_bp_work_safety_cert m where m.risk_district_id is not null", nativeQuery = true)
    List<BpWorkSafetyCert> findByArea();

    @Query(value = "select m from BpWorkSafetyCert m where m.state =?1 or m.state =?2")
    List<BpWorkSafetyCert> getCheckSafetys(int i, int i2);

    int countByCode(String s);

    @Query(value = "SELECT COUNT(*) FROM `wwyt_bp_work_safety_cert` WHERE RISK_DISTRICT_ID = ?1 AND (STATE = 2 OR STATE = 6) ",nativeQuery = true)
    int countByRiskDistrickId(Long id);

}
